<?php
declare(strict_types=1);

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
class module_excel{
    //写入文件
    /*
     * @param $title  标题
     * @param $data   数据  $data = [['id'=>1,'name'=>'sen1'],['id'=>2,'name'=>'sen2']];
     * @param $excel_name  excel文件名 $title = [['A','id','编号',0],['B','name','姓名',0]];
     */
    public function write_data($title,$data,$excel_name = '')
    {

        if(empty($data) || empty($title) || empty($excel_name)) return false;
        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();
        //设置当前工作表标题
        $activeWorksheet->setTitle($excel_name);
        $count = count($data);
        $styleArray = [
            'borders' => [
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => Color::COLOR_BLACK],
                ],
            ],
        ];
        foreach($title as $item){
            //cell_num为excel位置，cell_value为excel值
            $ColumnDimension = $item[0];
            $cell_title_num = $ColumnDimension . '1';
            $cell_title_value = $item[2];
            $activeWorksheet->setCellValue($cell_title_num, $cell_title_value);
            $column_length = $item[3];
            if($column_length){
                //设置列宽（手动）
                $activeWorksheet->getColumnDimension($ColumnDimension)->setWidth($column_length);
            }else{
                //设置列宽（自动调整）
                $activeWorksheet->getColumnDimension($ColumnDimension)->setAutoSize(true);
            }
            //设置边框样式
            $activeWorksheet->getStyle($cell_title_num . ':' . $cell_title_num)->applyFromArray($styleArray);
            for($i = 0;$i < $count;$i++){
                $cell_content_num = $ColumnDimension . ($i + 2);
                $cell_content_value = $data[$i][$item[1]];
                $activeWorksheet->setCellValue($cell_content_num, $cell_content_value);
                $activeWorksheet->getStyle($cell_content_num . ':' . $cell_content_num)->applyFromArray($styleArray);
            }
        }
        $highestRow = $activeWorksheet->getHighestRow(); // 总行数
        $highestColumn = $activeWorksheet->getHighestColumn(); // 总列数
        $area = 'A1:' . $highestColumn . $highestRow;
        //字体格式
        $activeWorksheet->getStyle($area)->getFont()->setSize(12);
        $activeWorksheet->getStyle($area)->getFont()->setBold(true);
//        $activeWorksheet->getStyle($area)->getFont()->setItalic(true);
        $activeWorksheet->getStyle($area)->getFont()->setName('Arial');
        $activeWorksheet->getStyle($area)->getFont()->setColor(new Color(Color::COLOR_BLUE));
        $activeWorksheet->getStyle($area)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        /*
            设置第10行行高为100pt：
            $activeWorksheet->getRowDimension('10')->setRowHeight(100);
            设置默认行高为15pt：
            $activeWorksheet->getDefaultRowDimension()->setRowHeight(15);
            将A18到E22合并为一个单元格:
            $activeWorksheet->mergeCells('A18:E22');
            将合并后的单元格拆分
            $activeWorksheet->unmergeCells('A18:E22');
        */
        //保存文件，没有目录创建目录
        $writer = new Xlsx($spreadsheet);
        $path =  date("Y") . "/" . date('Y-m-d') . "/" . $excel_name . '.xlsx';
        $filename = DIR_EXCEL . $path;
        module_mkdir::mkdir($filename);
        $writer->save('./public/excel/' . $path);
        return true;
    }
    //读取文件
    public function read_data($path)
    {
        $inputFileType = 'Xlsx';
        $inputFileName = $path;
        $reader = IOFactory::createReader($inputFileType);
        $spreadsheet = $reader->load($inputFileName);
        $worksheet = $spreadsheet->getActiveSheet();

        //$worksheet = $spreadsheet->getAllSheets();

        // 可以获取表格中任意单元格数据内容 $col表示单元格所在的列，A列表示第一列，$row表示所在的行。
        //$worksheet->getCellByColumnAndRow($col, $row)->getValue();

//        $highestRow = $worksheet->getHighestRow();
//        $highestColumn = $worksheet->getHighestColumn();
//        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
//
//        $data = [];
//        for ($row = 1; $row <= $highestRow; ++$row) {
//            for ($col = 1; $col <= $highestColumnIndex; ++$col) {
//                $data[$row][$col] = $worksheet->getCell(Coordinate::stringFromColumnIndex($col) . $row)->getValue();
//            }
//        }
        return $worksheet->toArray();
    }
    public function sheets()
    {
        $spreadsheet = new Spreadsheet();

        $spreadsheet->createSheet(1);
        $writer = new Xlsx($spreadsheet);

        $activeWorksheet_0 = $spreadsheet->getActiveSheet();
        $activeWorksheet_0->setTitle('Sheet1');
        $activeWorksheet_0->setCellValue('A1', 'Senxiaokai');

        $activeWorksheet_1 = $spreadsheet->getSheet(1);
        $activeWorksheet_1->setTitle('测试');
        $activeWorksheet_1->setCellValue('A1', 'Hello World!');

        $writer->save('./public/excel/2025/sheets.xlsx');
    }
}
